﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
using Modellayer;

namespace Dblayer
{
    public class DbSeat
    {
        SqlConnection con;

        public DbSeat()
        {
            con = new SqlConnection("Data Source=balder.ucn.dk;Initial Catalog=dm76_4;Persist Security Info=True;User ID=DM76_4;Password=MaaGodt");
        }

        public void createSeats(Show show)
        {
            try
            {
                con.Open();
                foreach (Seat s in show.seats)
                {
                    string insert = @"insert into Seat(SeatNumber, ReservationID, ShowId, Status) values ('" + s.seatNumber + "','" + s.reservationId + "','" + s.show.showId + "','" + s.status + "')";
                    SqlCommand myCommand = new SqlCommand(insert, con);
                    myCommand.ExecuteNonQuery();
                }

            }
            catch (Exception e)
            {
                Console.WriteLine("{0}", e.Message);
            }
            finally
            {
                if (con != null)
                {
                    con.Close();
                }
            }
        }

        public List<Seat> findSeats(Show show)
        {
            List<Seat> sList = new List<Seat>();
            SqlDataReader rdr = null;
            try
            {
                con.Open();
                string findQuery = @"select * from Seat where showId = '"+show.showId+"'";
                SqlCommand myCommand = new SqlCommand(findQuery, con);
                rdr = myCommand.ExecuteReader();
                while (rdr.Read())
                {
                    Seat s = new Seat();
                    s.seatNumber = int.Parse(rdr["Seatnumber"].ToString());
                    s.status = int.Parse(rdr["Status"].ToString());
                    s.show = show;
                    s.reservationId = int.Parse(rdr["ReservationID"].ToString());
                    sList.Add(s);
                }

            }
            catch (Exception e)
            {
                Console.WriteLine("{0}", e.Message);
                sList = null;
            }
            finally
            {
                if (rdr != null)
                {
                    rdr.Close();
                }


                if (con != null)
                {
                    con.Close();
                }

            }

            return sList;
        }

        public List<Seat> findSeatsUsingReservation(Reservation res)
        {
            List<Seat> sList = new List<Seat>();
            SqlDataReader rdr = null;
            try
            {
                con.Open();
                string findQuery = @"select * from Seat where showId = '" + res.show.showId + "' and ReservationID = '"+res.reservationsId+"'";
                SqlCommand myCommand = new SqlCommand(findQuery, con);
                rdr = myCommand.ExecuteReader();
                while (rdr.Read())
                {
                    Seat s = new Seat();
                    s.seatNumber = int.Parse(rdr["Seatnumber"].ToString());
                    s.status = int.Parse(rdr["Status"].ToString());
                    s.show = res.show;
                    s.reservationId = int.Parse(rdr["ReservationID"].ToString());
                    sList.Add(s);
                }

            }
            catch (Exception e)
            {
                Console.WriteLine("{0}", e.Message);
                sList = null;
            }
            finally
            {
                if (rdr != null)
                {
                    rdr.Close();
                }


                if (con != null)
                {
                    con.Close();
                }

            }

            return sList;
        } 

        public void UpdateSeat(Seat s)
        {
            try
            {
                con.Open();
                string updateQuery = @"update seat set ReservationID = '" + s.reservationId + "', Status = '" + s.status + "' where seat.SeatNumber = '" + s.seatNumber + "' and seat.showId ='" + s.show.showId +"'";
                SqlCommand myCommand = new SqlCommand(updateQuery, con);
                myCommand.ExecuteNonQuery(); 

            }
            catch (Exception e)
            {
                Console.WriteLine("{0}", e.Message);
            }
            finally
            {
                if (con != null)
                {
                    con.Close();
                }
            }
        }
    }
}
